import psycopg2
from psycopg2.extras import RealDictCursor

# 数据库连接参数
conn_params = {
    'dbname': 'your_dbname',
    'user': 'your_username',
    'password': 'your_password',
    'host': 'your_host',  # 通常是 'localhost' 或数据库服务器的IP地址
    'port': 'your_port'  # 默认是5432
}

class PgsqlHelper:
    def __init__(self,dbname,user,password,host,port=5432):
        self.conn_params =  {
            'dbname':dbname,
            'user': user,
            'password': password,
            'host': host,  # 通常是 'localhost' 或数据库服务器的IP地址
            'port':  port # 默认是5432
        }
        self.conn = psycopg2.connect(**self.conn_params)
        self.conn.cursor_factory = RealDictCursor
    def get_databases(self):
        sql = "SELECT datname as name  FROM pg_database ORDER BY datname;"
        cur = self.conn.cursor()
        # 执行SQL查询
        cur.execute(sql)
        rows = cur.fetchall()
        cur.close()
        return rows

    def get_tables(self):
        # 创建连接
        cur = self.conn.cursor()
        # 执行SQL查询
        cur.execute("""SELECT table_name  as name
            FROM information_schema.tables
            WHERE table_type = 'BASE TABLE'
              AND table_schema NOT IN ('pg_catalog', 'information_schema');""")
        # 获取查询结果
        rows = cur.fetchall()
        cur.close()
        return  rows

    def get_colum_list(self,name):
        # 创建连接
        cur = self.conn.cursor()
        sql = f"""
        SELECT  a.column_name AS "name", udt_name AS "col_type",
        (CASE
            WHEN COALESCE(character_maximum_length, -999) = -999 THEN numeric_precision
            ELSE character_maximum_length
        END)||(CASE
            WHEN udt_name IN ('numeric', 'decimal') THEN ','||numeric_scale::text
            ELSE '' end) AS "len",
        
        CASE
            WHEN is_nullable = 'YES' THEN '1'
            ELSE ''
        END AS "nullable",
        CASE
            WHEN pk.constraint_name IS NOT NULL THEN '1'
            ELSE ''
        END AS "pk",
        column_default AS "defval",
            col_description(b.oid, a.ordinal_position) AS "desc"
        FROM
            information_schema.columns AS a
        LEFT JOIN
            pg_class AS b ON a.table_name = b.relname
        LEFT JOIN
            information_schema.key_column_usage AS pk ON a.table_name = pk.table_name AND a.column_name = pk.column_name
        LEFT JOIN
            information_schema.table_constraints AS tc ON pk.constraint_name = tc.constraint_name
        WHERE
            a.table_name = '{name}'
        ORDER BY
            a.table_schema,
            a.table_name,
            a.ordinal_position;
        """
        # 执行SQL查询
        cur.execute( sql)
        # 获取查询结果
        rows = cur.fetchall()
        cur.close()
        return  rows

    def update_column_desc(self,table_name:str,col_name:str,desc:str):
        sql =f"""comment on column "{table_name}"."{col_name}" is '{desc}';"""
        cur = self.conn.cursor()
        # 执行SQL查询
        cur.execute(sql)
        cur.close()
        self.conn.commit()
        return {"code":1,"msg":"update ok"}

    def __del__(self):
        # 关闭cursor和连接
        self.conn.close()
        print('conn is close')
